﻿Imports Microsoft.VisualBasic
Imports Microsoft.VisualBasic.CompilerServices
Imports System
Imports System.Data.OleDb
Imports Microsoft.Office.Interop.Excel

Namespace ReportExcel
    <StandardModule> _
    Friend NotInheritable Class Module1
        ' Methods
        Public Shared Sub AddCellToSheet(ByRef sheet As Worksheet, ByVal intCol As Integer, ByVal intRow As Integer, ByVal strValue As String)
            Dim range As Range = DirectCast(sheet.Cells._Default(intRow, intCol), Range)
            range.Value = strValue
        End Sub

        Public Shared Function App_Path() As String
            Return AppDomain.CurrentDomain.BaseDirectory
        End Function

        Public Shared Function GetCellType(ByRef cell1 As Range) As Integer
            If (cell1 Is Nothing) Then
                Return 1
            End If
            Dim str As String = StringType.FromObject(cell1.NumberFormat)
            If (Strings.InStr(1, str, "#", 0) = 0) Then
                Return 1
            End If
            Return 2
        End Function

        Public Shared Function GetCellValueToFill(ByRef rs As OleDbDataReader, ByVal vtFieldList As Collection, ByVal strCellContent As String) As String
            Dim num As Integer = vtFieldList.Count
            If ((StringType.StrCmp(strCellContent, "", False) <> 0) And (num > 0)) Then
                Dim num3 As Integer = num
                Dim i As Integer = 1
                Do While (i <= num3)
                    Dim str2 As String = StringType.FromObject(vtFieldList.Item(i))
                    Dim str3 As String = StringType.FromObject(ObjectType.StrCatObj("", rs.Item(str2)))
                    strCellContent = Strings.Replace(strCellContent, ("${" & str2 & "}"), str3, 1, -1, 0)
                    i += 1
                Loop
            End If
            Return strCellContent
        End Function

        Public Shared Function IsLetter(ByVal strEval As String) As Boolean
            Return (((Strings.Asc(strEval) >= &H41) And (Strings.Asc(strEval) <= 90)) Or ((Strings.Asc(strEval) >= &H61) And (Strings.Asc(strEval) <= &H7A)))
        End Function

        Public Shared Function ToLetterCol(ByVal intCol As Integer) As Object
            Return StringType.FromChar(Strings.Chr(((&H41 + intCol) - 1)))
        End Function

        Public Shared Function ToLoc(ByVal intRow As Integer, ByRef intCol As Integer) As String
            Return (StringType.FromObject(Module1.ToLetterCol(intCol)) & StringType.FromInteger(intRow))
        End Function

        Public Shared Sub ToRowCol(ByVal strLoc As String, ByRef intRow As Integer, ByRef intCol As Integer)
            intCol = ((Strings.Asc(Strings.Left(strLoc, 1)) - &H41) + 1)
            intRow = IntegerType.FromString(Strings.Mid(strLoc, 2))
        End Sub

    End Class
End Namespace

